Data Cleaning: Eurovision
Max Glonek
Trimester 1, 2025
1 Rule 1: Look at the Data
2 Rule 2: Is There a Package to Deal With That
songs <- jsonlite::fromJSON(glue::glue("./data/eurovision-lyrics.json"))3 Rule 3: Break it Into Pieces
songs[[1]]## $`#`
## [1] "1"
##
## $Country
## [1] "Netherlands"
##
## $`#.1`
## [1] "1"
##
## $Artist
## [1] "Jetty Paerl"
##
## $Song
## [1] "De vogels van Holland"
##
## $Language
## [1] "Dutch"
##
## $Pl.
## [1] "-"
##
## $Sc.
## [1] "-"
##
## $Eurovision_Number
## [1] 1
##
## $Year
## [1] "1956"
##
## $Host_Country
## [1] "Switzerland"
##
## $Host_City
## [1] "Lugano"
##
## $Lyrics
## [1] "De vogels van Holland zijn zo muzikaal\nZe leren in hun prille jeugd al tierelieren\nDe merel, de lijster en de nachtegaal\nOm zo de lente in Holland goed te kunnen vieren\n\nHet is geen wonder, want nergens\nZijn de plassen zo blauw\nAls in Holland - mijnheer\nAls in Holland - mevrouw\nHet is geen wonder, want nergens\nIs het gras zo vol dauw\nZijn de meisjes zo lief\nZijn de meisjes zo trouw\nEn daarom zijn de vogels hier allemaal\nZo muzikaal, zo muzikaal, zo muzikaal\n\nDe hele wereld door heb ik vogels horen zingen\nIn het zuiden, in het westen, in het noorden\nIn vele verre landen heb ik vogels horen zingen\nZij zingen kleine liedjes zonder woorden\n\nDe Franse vogels zingen \"tudeludelu\"\nJapanse vogels zingen \"tudeludelu\"\nChinese vogels zingen \"tudeludelu\"\nMaar de vogels zingen nergens\nZo gelukkig en blij\nAls in Holland in het voorjaar in de wei\n\nDe vogels van Holland zijn zo muzikaal\nZe leren in hun prille jeugd al tierelieren\nDe merel, de lijster en de nachtegaal\nOm zo de lente in Holland goed te kunnen vieren\n\nHet is geen wonder, want nergens\nZijn de plassen zo blauw\nAls in Holland - mijnheer\nAls in Holland - mevrouw\nHet is geen wonder, want nergens\nIs het gras zo vol dauw\nZijn de meisjes zo lief\nZijn de meisjes zo trouw\nEn daarom zijn de vogels hier allemaal\nZo muzikaal, zo muzikaal, zo muzikaal"
##
## $`Lyrics translation`
## [1] "The birds of Holland are so musical\nThey already learn to twitter in their early youth\nThe blackbird, the thrush and the nightingale\nSo they can celebrate spring in Holland It's no wonder, because nowhere the puddles are so blue\nAs in Holland, sir\nAs in Holland, madam\nIt's no wonder, because nowhere the grass is so full of dew Are the girls so sweet, are the girls so faithfull\nAnd that's why all the birds here are\nSo musical, So musical, So musical Across the whole world I've heard birds sing\nTo the south, to the west, to the north\nIn many faraway countries I've heard birds sing\nThey sing little songs without words The French birds sing toodledoo\nJapanese birds sing toodledoo\nChinese birds sing toodledoo\nBut nowhere the birds sing so happily and cheerfully\nAs in Holland in spring in the meadow The birds of Holland are so musical\nThey already learn to twitter in their early youth\nThe blackbird, the thrush and the nightingale\nSo they can celebrate the spring in Holland It's no wonder, because nowhere the puddles are so blue\n As in Holland, sir\n As in Holland, madam\nIt's no wonder, because nowhere the grass is so full of dew Are the girls so sweet, are the girls so faithfull\nAnd that's why all the birds here are\nSo musical, So musical, So musical"parse_song <- function(song){
country <- song$Country
artist <- song$Artist
title <- song$Song
year <- song$Year
lyrics <- song$Lyrics
translation <- song$`Lyrics translation`
place <- song$Pl.
tibble(
country, artist, title, year, lyrics, translation, place
)
}
parse_song(songs[[1]])4 Rule 4: Get it Into a Tibble
songs <-
songs %>%
map_df(parse_song, .id = "ID")
songs5 Rule 5: Look at Each Column
We need to make sure all of our data makes sense in context. You can find the source data at this link: https://www.kaggle.com/minitree/eurovision-song-lyrics
5.1 ID
This is an ID number assigned to each song. We should check that the song IDs are all unique.
songs %>% count(ID) %>% filter(n > 1)They are unique, but they’re also not useful, so delete them.
songs <-
songs %>%
select(-ID)
songs5.2 Artist
Are there any artists who appear more than once?
songs %>%
count(artist) %>%
arrange(-n)songs %>%
filter(str_detect(artist, "Jed"))songs %>%
filter(country == "Australia")5.3 Title
songs %>%
count(title) %>%
arrange(-n)5.4 Year
range(songs$year)## [1] "1956" "2021"songs %>%
count(year) %>%
ggplot(aes(year, n)) +
geom_point() +
theme(axis.text.x = element_text(angle = -90, hjust=0))
5.5 Lyrics
songs %>%
sample_n(5) %>%
pull(lyrics) %>%
cat(sep = "\n\n\n")## Πέρασε κι άλλη μέρα χωρίς σκοπό
## Άδειες οι ώρες μόνη στον κόσμο αυτό
## Μέσα στην κάμαρά μου, σκυφτή ξανά
## Μοναδική χαρά, να θυμάμαι τα παλιά
##
## Τέλειωσες σαν τραγούδι μελωδικό
## Ήσουν ένα μικρό κομμάτι μουσικό
## Έμεινε τ' άρωμά σου σαν νότα στο χαρτί
## Σφραγιδα ζωντανή στιγμής ερωτικής
##
## Σ' αγαπούσα, στη ζωή μου σε κρατούσα
## Έχεις φύγει και με πνίγει η μοναξιά
## Να σε κλείσω στην καρδιά μου λαχταρούσα
## Μα ήταν λάθος, το κατάλαβα αργά
##
## Πάντα θα σε θυμάμαι σα μια γιορτή
## Εικόνα αγαπημένη, παντοτινή
## Ανάμνηση γλυκιά που δεν θα σβήσεις
## Ελπίδα μυστική πως θα γυρίσεις
##
## Σ' αγαπούσα, στη ζωή μου σε κρατούσα
## Έχεις φύγει και με πνίγει η μοναξιά
## Να σε κλείσω στην καρδιά μου λαχταρούσα
## Μα ήταν λάθος, το κατάλαβα αργά
##
## Να σε κλείσω στην καρδιά μου λαχταρούσα
## Μα ήταν λάθος, το κατάλαβα αργά
##
##
## (With love) Hey hey hey with love
## (With love) Hey hey heeeey whoooa ohhh
## (With love) Whoa whoaa whoaaaa
##
## With love! In my mind, my body and my soul
## I'm doing everything and I'm doing it all
## With love!
##
## In my mind, my body and my soul
## With love, baby, you can have it all
##
## If you like to move it, if you like to groove it
## Do it with love, baby
## If you like to sing it, if you like to swing it
## Do it with love, baby
##
## Cause when your love is gone
## And you're all alone
## There is nothing left to carry on
## So whenever we're singing this song we do it
## With love, baby!
##
## With love
## In my mind, my body and my soul
## I'm doing everything and I'm doing it all
## With love
## In my mind, my body and my soul
## With love, baby, you can have it all
##
## With love, With love baby (x3)
## Oh, whatever you're doing just do it with love!
##
## You gotta do it with love
## Come on and sing it, swing it, with love
## Feel it in your body, your soul and your mind
## When you move, when you groove, with love
##
## Yes, I had to sing it and I came to bring it to you
## With love, baby
## Then I tried to blend it but I have to end it for you
## With love, baby
##
## Cause when I love you and you love me too
## There is nothing left for us to do
## But to hug and to kiss and to tug into bliss
## With love, baby
##
## With love
## In my mind, my body and my soul
## I'm doing everything and I'm doing it all
## With love
## In my mind, my body and my soul
## With love, baby, you can have it all
##
## With love, With love baby (x3)
## Oh, whatever you're doing just do it with love
##
## In my mind, my body and my soul
## I'm doing everything and I'm doing it all
##
## With love, baby, you can have it all
##
## With love, With love baby (x3)
## Oh, whatever you're doing just do it
## With love
##
##
## Did somebody dare to tell what's right
## Which colours to wear to laugh or cry
## You want to be fair and maybe you're right
## Lets burn our fingers stop the fight
##
## Always driving through the red lights babe
## Everybody stops but me
##
## Just run away to the stars
## Just run away 'til you're high enough
## I will be waiting above
## And make that happen I tried so hard
##
## How lucky you are your statement in fine
## Because what you think is what you find
##
## Always driving through the red lights babe
## Everybody stops but me
##
## Just run away to the stars
## Just run away 'til you're high enough
## I will be waiting above
## And make that happen I tried so hard
##
## Just run away to the stars
## Just run away 'til you're high enough
## I will be waiting above
## And make that happen I tried so hard
##
## Did somebody say it's gonna be easy
## Til you get to where you do belong
## Your newly opened mind explains the reasons
## Why you have to carry on
##
## Just run away to the stars
## Just run away 'til you're high enough
## I will be waiting above
## And make that happen I tried so hard
##
## Just run away to the stars
## Just run away 'til you're high enough
## I will be waiting above
## And make that happen I tried so hard
##
##
## Yo soy aquel
## Que cada noche te persigue
## Yo soy aquel
## Que por quererte ya no vive
## El que te espera, el que te sueña
## El que quisiera ser el dueño
## De tu amor, de tu amor
##
## Yo soy aquel
## Que por quererte da la vida
## Yo soy aquel
## Que estando lejos no te olvida
## El que te espera, el que te sueña
## Aquel que reza cada noche por tu amor
##
## Y estoy aquí, aquí para quererte
## Estoy aquí, aquí para adorarte
## Yo estoy aquí, aquí para decirte:
## Que como yo nadie te amo
##
## Yo soy aquel
## Que por tenerte da la vida
## Yo soy aquel
## Que estando lejos no te olvida
## El que te espera, el que te sueña;
## Aquel que reza cada noche por tu amor
##
## Y estoy aquí, aquí para quererte
## Estoy aquí, aquí para adorarte
## Yo estoy aquí, aquí para decirte:
## Amor, Amor, Amor, Amor
##
##
## Some people let go
## Some people let love slip away
## But I'm gonna hold on
## When no one believes I'll keep the faith
## And I know that we're in trouble
## But I swear that we'll survive
## If we were deep sea divers
## And no one came to find us
## If you had nothing left
## I'd give you my last breath
## I'd give you my last breath
## We all need a lifeline
## Someone to call when we're alone
## It's all good in the good times
## But I'll still be there when it all goes wrong, yes I will
## If we were deep sea divers
## And no one came to find us
## If you had nothing left
## I'd give you my last breath
## I'd give you my last breath
## And I know that we're in trouble,
## But I swear that we'll survive
## If we were deep sea divers
## And no one came to find us
## If you had nothing left
## I'd give you my last breath
## I'd give you my last breath
## I'd give you my last breath5.6 Translation
songs %>%
sample_n(5) %>%
pull(translation) %>%
cat(sep = "\n\n\n")## God knows that I wanted to be happy with you
## To live in the sunlight of your most tender look
## Now your desire is dead, I live my own desire
## Blooming spring in the winter sun
## Dreams that I dreamt, where are they?
## Hours that I lived, who has them?
## What’s the use of having a heart, and not having anyone’s love? Kisses that I gave you, where are they?
## Who have you given what belonged to me?
## It’s better not to have a heart, than having it and not having it, like me I bartered everything in my life for nothing
## Defeated flag, torn in the ground
## I’m the forgotten date, the lost thing that goes on auction Dreams that I dreamt, where are they?
## Hours that I lived, who has them?
## What’s the use of having a heart, and not having anyone’s love?
## I live of longings, my love, my life has lost the splendour
## Like a winter sun I have no warmth What’s the use of having a heart, and not having anyone’s love?
## I live of longings, my love, my life has lost the splendour
## Like a winter sun I have no warmth
##
##
## English
##
##
## If I sometimes look like a liar to you, you don't know
## There's an honest world that never lies When a moonbeam
## Notices me through the sky and the stars
## And a breeze
## Makes time lose its haste When a moonbeam
## Wipes away every shadow of doubt and deceit
## I'm more free and true, I fly towards you, wholehearted You're mistaken if you don't believe in yourself
## If I sometimes – it's normal – see only myself But there's a moonbeam
## That I'm impatiently waiting for every night because
## It'll take me back to a time
## That is raised for the love that only belongs to you When a moonbeam
## Can give the thought the biggest wings that it's got
## I'm more free and true, I fly towards you, wholehearted A moonbeam is enough
## To turn the sea into jazz music
## And to stay alone both at home and outside
## But it's always you and I When a moonbeam
## Ad-libs a different note for us every time
## That it'll be our song, night after night
## That it'll be our song, night after night
##
##
## English
##
##
## English5.7 Place
songs %>% count(place)length(unique(songs$year))## [1] 66songs %>%
count(year, place) %>%
filter(place == "1") %>%
arrange(-n)According to Wikpedia, there were four winners in 1969:
https://en.wikipedia.org/wiki/Eurovision_Song_Contest
On only one occasion have multiple winners been declared in a single contest: in 1969, four countries finished the contest with an equal number of votes and due to the lack of a tie-break rule at the time, all four countries were declared winners.[7][157]
6 Rule 6: You May Need to Go Back
Our original parse function just grabbed translation as
is. After looking at translation, we can see that this
variable says English if the original song lyrics are in
English. English.
7 Rule 7: Create New Columns?
7.1 Decade
songssongs <-
songs %>%
mutate(
year = parse_number(year),
decade = year - year %% 10
)
songs %>%
ggplot(aes(year, decade)) +
geom_point()
7.2 Lyrics
songs %>%
count(translation) %>%
arrange(-n)songs <-
songs %>%
mutate(
english_lyrics = ifelse(
translation == "English",
lyrics, translation
)
)8 Rule 7: Save the Data and Write it Up
It’s critically important to make detailed notes about any changes you have made when cleaning the data.
filenamepath <- glue::glue("./data/{lubridate::today()}-eurovision.rds")
filenamepath## ./data/2025-02-11-eurovision.rdswrite_rds(songs,filenamepath)